package DBHandle;

import java.io.*;
import java.sql.*;

import com.opensymphony.xwork2.Action;
import com.opensymphony.xwork2.ActionContext;

//添加新商品到数据库
public class EditProduct
{
	//变量定义
	private InputStream inputStream;
	DBConn.DBConnManager db=new DBConn.DBConnManager();
	DBHandle.General dbHandle=new DBHandle.General();
	Statement stmt=null;
	ResultSet rs=null;
	String sql="";	
	Connection conn=db.getConnection("Mysql");
	
	public InputStream getInputStream() {
		return this.inputStream;
	}
	
	//检查Product是否已存在 
	public String CheckProduct() throws Exception
	{
		String[] name=(String[])ActionContext.getContext().getParameters().get("item");
		if(name!=null&&conn!=null)
		{
			sql="select id from qbx_product where item = '"+name[0]+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			rs.last();
			if(rs.getRow()==1)
				inputStream=new StringBufferInputStream(String.valueOf(rs.getInt("id")));
			else 
				inputStream=new StringBufferInputStream("false");
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//查询Product信息
	public String SelectProduct() throws Exception
    {
    	String[] category=(String[])ActionContext.getContext().getParameters().get("category");
    	String[] item=(String[])ActionContext.getContext().getParameters().get("item");
    	String[] weight_1=(String[])ActionContext.getContext().getParameters().get("weight_1");
    	String[] weight_2=(String[])ActionContext.getContext().getParameters().get("weight_2");
    	String[] volume_1=(String[])ActionContext.getContext().getParameters().get("volume_1");
    	String[] volume_2=(String[])ActionContext.getContext().getParameters().get("volume_2");
    	String[] height_1=(String[])ActionContext.getContext().getParameters().get("height_1");
    	String[] height_2=(String[])ActionContext.getContext().getParameters().get("height_2");
    	String[] width_1=(String[])ActionContext.getContext().getParameters().get("width_1");
    	String[] width_2=(String[])ActionContext.getContext().getParameters().get("width_2");
    	String[] depth_1=(String[])ActionContext.getContext().getParameters().get("depth_1");
    	String[] depth_2=(String[])ActionContext.getContext().getParameters().get("depth_2");
    	String[] price_1=(String[])ActionContext.getContext().getParameters().get("price_1");
    	String[] price_2=(String[])ActionContext.getContext().getParameters().get("price_2");
    	String[] arrayList={weight_1[0],weight_2[0],volume_1[0],volume_2[0],height_1[0],height_2[0],width_1[0],
    			width_2[0],depth_1[0],depth_2[0],price_1[0],price_2[0]};
    	String[] param={"weight","weight","volume","volume","height","height","width","width","depth","depth","averageprice","averageprice"};
    	sql="select id,item,category_id,weight,volume,height,width,depth,cost,averageprice from qbx_product where ";
    	if(category!=null&&(String)category[0]!="")
    	{
    		int categoryID=dbHandle.SelectID("qbx_product_category", "name", category[0]);
    		sql+="category_id = "+categoryID+" and ";
    	}
    	if(item!=null&&(String)item[0]!="")
    		sql+="item = '"+item[0]+"' and ";
    	for(int i=0;i<12;)
    	{
    		if(arrayList[i]!=""&&arrayList[i+1]!="")
        	{
        		int j=Float.compare(Float.parseFloat(arrayList[i]),Float.parseFloat(arrayList[i+1]));
        		if(j>0)
        		{
        			String tmp=arrayList[i];
        			arrayList[i]=arrayList[i+1];
        			arrayList[i+1]=tmp;
        		}
        		sql+=param[i]+" between "+arrayList[i]+" and "+arrayList[i+1]+" and ";
        	}
        	else if(arrayList[i]!="")
        	{
        		sql+=param[i]+" >= "+arrayList[i]+" and ";
        	}
        	else if(arrayList[i+1]!="")
        	{
        		sql+=param[i]+" <= "+arrayList[i+1]+" and ";
        	}
    		i=i+2;
    	}
    	sql+="1=1";
    	if(conn!=null)
		{
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
            String aaDataString="{\"aaData\":[";
			while(rs.next())
			{
			aaDataString+="[\"<input type='checkbox' class='checkOne' value='"+rs.getInt("id")+"'></input>\",\""
						+rs.getString("item")+"\",\""+SelectcategoryByID(rs.getInt("category_id"))
						+"\",\""+String.valueOf(rs.getFloat("weight"))+"\",\""+String.valueOf(rs.getFloat("volume"))
						+"\",\""+String.valueOf(rs.getFloat("height"))+"\",\""+String.valueOf(rs.getFloat("width"))
						+"\",\""+String.valueOf(rs.getFloat("depth"))+"\",\""+String.valueOf(rs.getFloat("cost"))
						+"\",\""+String.valueOf(rs.getFloat("averageprice"))+"\",\""
						+"<a href='#' id='editOne' value='"+rs.getInt("id")+"'>Edit</a>&nbsp&nbsp"
						+"<a href='#' id='deleteOne' value='"+rs.getInt("id")+"'>Delete</a>"
						+"\"],";
			}
			if(aaDataString.substring(aaDataString.length()-1).equals(","))
				aaDataString=aaDataString.substring(0,aaDataString.length()-1);
			aaDataString+="]}";
			inputStream=new StringBufferInputStream(aaDataString);
			rs.close();
			stmt.close();
			db.releaseConnection("Mysql", conn); 
		}          
        return Action.SUCCESS;  
    }  
	
	//获取特定Product信息
	public String SelectProductByID() throws Exception
	{
		String[] ID=(String[])ActionContext.getContext().getParameters().get("id");
		sql="select item,category_id,weight,volume,height,width,depth from qbx_product where id = '"+ID[0]+"'";
		stmt=conn.createStatement();
		rs=stmt.executeQuery(sql);
		while (rs.next())
		{
			String categoryString=SelectcategoryByID(rs.getInt("category_id"));
			String tempString="{'item':'"+rs.getString("item")+"','category':'"+categoryString;
			tempString+="','weight':'"+rs.getString("weight")+"','volume':'"+rs.getString("volume");
			tempString+="','height':'"+rs.getString("height")+"','width':'"+rs.getString("width");
			tempString+="','depth':'"+rs.getString("depth")+"'}";
			inputStream=new StringBufferInputStream(tempString);
		}
		rs.close();
		stmt.close();
		db.releaseConnection("Mysql", conn); 
		return Action.SUCCESS;
	}
	
	//保存Product信息
	public String SaveProduct() throws Exception
	{
		//获取请求参数
		String[] item=(String[])ActionContext.getContext().getParameters().get("item");
		String[] category=(String[])ActionContext.getContext().getParameters().get("category");
		String[] weight=(String[])ActionContext.getContext().getParameters().get("weight");
		String[] volume=(String[])ActionContext.getContext().getParameters().get("volume");
		String[] height=(String[])ActionContext.getContext().getParameters().get("height");
		String[] width=(String[])ActionContext.getContext().getParameters().get("width");
		String[] depth=(String[])ActionContext.getContext().getParameters().get("depth");
		//查询商品类别ID
		int categoryID=dbHandle.SelectID("qbx_product_category", "name", category[0]);
		
		//请求数据库连接
		if(conn!=null)
		{
			sql="insert into qbx_product(item,category_id,weight,volume,height,width,depth) values('"+
				item[0]+"',"+categoryID+","+Float.parseFloat(weight[0])+","+Float.parseFloat(volume[0])+
				","+Float.parseFloat(height[0])+","+Float.parseFloat(width[0])+","+Float.parseFloat(depth[0])+")";
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//更新Product信息
	public String UpdateProduct() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");
		String[] item=(String[])ActionContext.getContext().getParameters().get("item");
		String[] category=(String[])ActionContext.getContext().getParameters().get("category");
		String[] weight=(String[])ActionContext.getContext().getParameters().get("weight");
		String[] volume=(String[])ActionContext.getContext().getParameters().get("volume");
		String[] height=(String[])ActionContext.getContext().getParameters().get("height");
		String[] width=(String[])ActionContext.getContext().getParameters().get("width");
		String[] depth=(String[])ActionContext.getContext().getParameters().get("depth");
		//查询商品类别ID
		int categoryID=dbHandle.SelectID("qbx_product_category", "name", category[0]);
		
		//请求数据库连接
		if(conn!=null)
		{
			sql="update qbx_product set item = '"+item[0]+"',category_id ="+categoryID+",weight = "+Float.parseFloat(weight[0])
			    +",volume = "+Float.parseFloat(volume[0])+",height = "+height[0]+",width = "+width[0]+",depth = "+depth[0]
			    +" where id = "+Integer.parseInt(id[0]);
			stmt=conn.createStatement();
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//删除Product信息 
	public String DeleteOneProduct() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			stmt=conn.createStatement();			
			sql="delete from qbx_aisles where product_id = '"+id[0]+"'";
			int j=stmt.executeUpdate(sql);
			sql="delete from qbx_inventory where product_id = '"+id[0]+"'";
			int k=stmt.executeUpdate(sql);
			sql="delete from qbx_product where id = '"+id[0]+"'";
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	public String DeleteMoreProduct() throws Exception
	{
		//获取请求参数
		String[] id=(String[])ActionContext.getContext().getParameters().get("id");		
		//请求数据库连接
		if(conn!=null)
		{
			String[] ID=id[0].split("#");
			for(int m=0;m<ID.length;m++)
			{
			stmt=conn.createStatement();			
			sql="delete from qbx_aisles where product_id = '"+ID[m]+"'";
			int j=stmt.executeUpdate(sql);
			sql="delete from qbx_inventory where product_id = '"+ID[m]+"'";
			int k=stmt.executeUpdate(sql);
			sql="delete from qbx_product where id = '"+ID[m]+"'";
			int i=stmt.executeUpdate(sql);
			if(i==1)
				inputStream=new StringBufferInputStream("true");
			else 
				inputStream=new StringBufferInputStream("false");
			stmt.close();
			db.releaseConnection("Mysql", conn);
			}
		}
		else
		{
			inputStream=new StringBufferInputStream("error");
		}
		return Action.SUCCESS;
	}
	
	//获取商品类别
	 private String SelectcategoryByID(int id) throws Exception
	    {
	    	String temp="";
	    	ResultSet rsResultSet=null;
			if(conn!=null)
			{			
				sql="select name from qbx_product_category where id = "+id;
				stmt=conn.createStatement();
				rsResultSet=stmt.executeQuery(sql);
				while(rsResultSet.next())
					temp=rsResultSet.getString(1);
			}
			rsResultSet.close();
			return temp;
	    }
}